from datetime import datetime, time

from sqlalchemy import bindparam, func, or_, select, update, delete  # noqa: F401
from sqlalchemy.ext.asyncio import AsyncSession

from module_kylin.entity.do.future_daily_quote_do import KylinFutureDailyQuote
from module_kylin.entity.vo.future_daily_quote_vo import FutureDailyQuoteModel, FutureDailyQuotePageQueryModel
from utils.page_util import PageUtil


class FutureDailyQuoteDao:
    """
    期货日线行情数据库操作层
    """

    @classmethod
    async def get_quote_detail_by_id(cls, db: AsyncSession, id: int):
        """
        根据行情id获取行情信息

        :param db: orm对象
        :param id: 行情id
        :return: 行情信息对象
        """
        quote_info = (
            (await db.execute(select(KylinFutureDailyQuote).where(KylinFutureDailyQuote.id == id))).scalars().first()
        )

        return quote_info

    @classmethod
    async def get_quote_detail_by_info(cls, db: AsyncSession, quote: FutureDailyQuoteModel):
        """
        根据参数配置参数获取参数配置信息

        :param db: orm对象
        :param quote: 参数配置参数对象
        :return: 参数配置信息对象
        """
        quote_info = (
            (
                await db.execute(
                    select(KylinFutureDailyQuote).where(
                        KylinFutureDailyQuote.code == quote.code if quote.code else True,
                        KylinFutureDailyQuote.date == quote.date if quote.date else True,
                    )
                )
            )
            .scalars()
            .first()
        )
        return quote_info

    @classmethod
    async def get_quote_list(
        cls, db: AsyncSession, query_object: FutureDailyQuotePageQueryModel, is_page: bool = False
    ):
        """
        根据查询参数获取期货日线行情列表信息

        :param db: orm对象
        :param query_object: 查询参数对象
        :param is_page: 是否开启分页
        :return: 期货日线行情列表信息对象
        """
        query = (
            select(KylinFutureDailyQuote)
            .where(
                KylinFutureDailyQuote.code.like(f'%{query_object.code}%') if query_object.code else True,
                KylinFutureDailyQuote.date.between(
                    datetime.strptime(query_object.begin_date, '%Y-%m-%d').date(),
                    datetime.strptime(query_object.end_date, '%Y-%m-%d').date(),
                )
                if query_object.begin_date and query_object.end_date
                else True,
            )
            .order_by(KylinFutureDailyQuote.code, KylinFutureDailyQuote.date.desc())
            .distinct()
        )
        quote_list = await PageUtil.paginate(db, query, query_object.page_num, query_object.page_size, is_page)

        return quote_list

    @classmethod
    async def get_quote_all(cls, db: AsyncSession):
        """
        获取所有期货日线行情信息

        :param db: orm对象
        :return: 期货日线行情列表信息对象
        """
        quote_list = (await db.execute(select(KylinFutureDailyQuote))).scalars().all()
        return quote_list

    @classmethod
    async def add_quote(cls, db: AsyncSession, quote: FutureDailyQuoteModel):
        """
        新增期货日线行情信息

        :param db: orm对象
        :param quote: 期货日线行情信息对象
        :return: 新增期货日线行情信息对象
        """
        db_quote = KylinFutureDailyQuote(**quote.model_dump())
        db.add(db_quote)
        await db.flush()
        return quote

    @classmethod
    async def edit_quote(cls, db: AsyncSession, quote: dict):
        """
        编辑期货日线行情信息

        :param db: orm对象
        :param quote: 期货日线行情信息对象
        :return: 编辑期货日线行情信息对象
        """
        await db.execute(update(KylinFutureDailyQuote), [quote])

    @classmethod
    async def delete_quote(cls, db: AsyncSession, quote: FutureDailyQuoteModel):
        """
        删除期货日线行情数据库操作

        :param db: orm对象
        :param quote: 期货日线行情对象
        :return:
        """
        await db.execute(delete(KylinFutureDailyQuote).where(KylinFutureDailyQuote.id.in_([quote.id])))

    @classmethod
    async def delete_quote_by_code(cls, db: AsyncSession, code: str):
        """
        根据合约代码删除所有相关的期货日线行情数据库操作

        :param db: orm对象
        :param code: 期货合约代码
        :return:
        """
        await db.execute(delete(KylinFutureDailyQuote).where(KylinFutureDailyQuote.code == code))

    @classmethod
    async def get_weighted_average_close_price(cls, db: AsyncSession, code: str, days: int):
        """
        计算特定期货代码和天数的收盘价格加权平均价格
        计算规则：sum（收盘价*成交量）/sum（成交量）

        :param db: orm对象
        :param code: 期货合约代码
        :param days: 计算天数
        :return: 加权平均价格
        """
        # 计算总成交量和总价量乘积
        # 先获取最近指定天数的数据
        subquery = (
            select(KylinFutureDailyQuote.date)
            .filter(KylinFutureDailyQuote.code == code)
            .order_by(KylinFutureDailyQuote.date.desc())
            .limit(days)
            .subquery()
        )

        # 计算加权平均价格
        result = await db.execute(
            select(
                (
                    func.sum(KylinFutureDailyQuote.close * KylinFutureDailyQuote.volume)
                    / func.sum(KylinFutureDailyQuote.volume)
                ).label('weighted_avg')
            ).filter(KylinFutureDailyQuote.code == code, KylinFutureDailyQuote.date.in_(subquery))
        )

        weighted_avg = result.scalar_one_or_none()
        return weighted_avg
